use common
go

IF OBJECT_ID('dbo.p_ParseString') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.p_ParseString
    IF OBJECT_ID('dbo.p_ParseString') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.p_ParseString >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.p_ParseString >>>'
END
go

create proc p_ParseString
(
    @TempValue	varchar(16384),		-- List of values separated with delimiter
	@Separator	char(1),		-- Delimiter
	@TableName	varchar(255)=null	-- use this table name to insert values if it's omitted
) as
begin
-- declare 
        declare @ComaPosition   int		-- first occurrence of @Separator in the @TempValue
	declare @TempToken	varchar(255)	-- variable to hold token value in the @TempValue

-- initialize
	create table #temp (Token varchar(255))

	if @TableName is null select @TableName="#value"

-- process
	while charindex(@Separator,@TempValue)>0
	begin
		select @ComaPosition=charindex(@Separator,@TempValue)
		select @TempToken = substring(@TempValue,1,@ComaPosition-1)
		select @TempValue = right(@TempValue,char_length(@TempValue)-@ComaPosition)

		if @TempToken is not null insert into #temp values(@TempToken)
	end

	if @TempValue is not null insert into #temp values(@TempValue)

	exec("insert into " + @TableName + " select distinct ltrim(rtrim(Token)) from #temp")

        return 0
end
go
EXEC sp_procxmode 'dbo.p_ParseString','unchained'
go
IF OBJECT_ID('dbo.p_ParseString') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.p_ParseString >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.p_ParseString >>>'
go
GRANT EXECUTE ON dbo.p_ParseString TO public
go